# -*- coding:utf-8 -*-

__author__ = 'zhl'
"""
专题报表-沪深港股通-港资机构持股明细
"""
from EmQuantAPI import *
from datetime import timedelta, datetime
import time as _time
import traceback
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import datetime


"""
创建Pandas DataFrame 数据结构
label=list()
dict={0:[],1:[]}
"""


def dict_convert_dataframe_append_toscv(file, dict, label, df):

    for k, v in dict.items():
        data = [v]
        df = df.append(pd.DataFrame(data, index=[k], columns=label))
        df.to_csv(file, mode="a", header=False)
    return df


def dict_convert_dataframe(dict, label, df):

    for k, v in dict.items():
        data = [v]
        df = df.append(pd.DataFrame(data, index=[k], columns=label))
    return df


def mainCallback(quantdata):
    """
    mainCallback 是主回调函数，可捕捉如下错误
    在start函数第三个参数位传入，该函数只有一个为c.EmQuantData类型的参数quantdata
    :param quantdata:c.EmQuantData
    :return:
    """
    print("mainCallback", str(quantdata))
    # 登录掉线或者 登陆数达到上线（即登录被踢下线） 这时所有的服务都会停止
    if str(quantdata.ErrorCode) == "10001011" or str(quantdata.ErrorCode) == "10001009":
        print("Your account is disconnect. You can force login automatically here if you need.")
    # 行情登录验证失败（每次连接行情服务器时需要登录验证）或者行情流量验证失败时，会取消所有订阅，用户需根据具体情况处理
    elif str(quantdata.ErrorCode) == "10001021" or str(quantdata.ErrorCode) == "10001022":
        print("Your all csq subscribe have stopped.")
    # 行情服务器断线自动重连连续6次失败（1分钟左右）不过重连尝试还会继续进行直到成功为止，遇到这种情况需要确认两边的网络状况
    elif str(quantdata.ErrorCode) == "10002009":
        print("Your all csq subscribe have stopped, reconnect 6 times fail.")
        # 行情订阅遇到一些错误(这些错误会导致重连，错误原因通过日志输出，统一转换成EQERR_QUOTE_RECONNECT在这里通知)，正自动重连并重新订阅,可以做个监控
    elif str(quantdata.ErrorCode) == "10002012":
        print("csq subscribe break on some error, reconnect and request automatically.")
        # 资讯服务器断线自动重连连续6次失败（1分钟左右）不过重连尝试还会继续进行直到成功为止，遇到这种情况需要确认两边的网络状况
    elif str(quantdata.ErrorCode) == "10002014":
        print("Your all cnq subscribe have stopped, reconnect 6 times fail.")
    # 资讯订阅遇到一些错误(这些错误会导致重连，错误原因通过日志输出，统一转换成EQERR_INFO_RECONNECT在这里通知)，正自动重连并重新订阅,可以做个监控
    elif str(quantdata.ErrorCode) == "10002013":
        print("cnq subscribe break on some error, reconnect and request automatically.")
    # 资讯登录验证失败（每次连接资讯服务器时需要登录验证）或者资讯流量验证失败时，会取消所有订阅，用户需根据具体情况处理
    elif str(quantdata.ErrorCode) == "10001024" or str(quantdata.ErrorCode) == "10001025":
        print("Your all cnq subscribe have stopped.")
    else:
        pass


def startCallback(message):
    print("[EmQuantAPI Python]", message)
    return 1


def getStockDataInfo(secucode=None, traddate=None):
    """
    获取 股票数据
    """
    if secucode is None:
        secucode = ''
    if traddate is None:
        traddate = ''
        # TODO

    options = "SECUCODE="+secucode+",TRADEDATE="+traddate

    # 2021-03-14 09:44:15
    # 沪深股通标的港股机构持股明细 参数: 证券代码 交易日期 字段: 证券代码 证券简称 参与者名称 持股量(股) 占已发行股份(%)
    data_res = c.ctr("HKInstHoldingDetailInfo", "SECUCODE,SECURITYNAME,PARTICIPANTNAME,SHAREHOLDING,SHARESRATE",
                     options)
    return data_res


def getDateOfCycle(start_date=None, end_date=None):
    """
    获取日期范围内的 股票交易周期数
    返回日期列表
    """
    if start_date is None:
        start_date = "2020-01-01"
    if end_date is None:
        end_date = datetime.datetime.now().strftime('%Y-%m-%d')
    # 2021-03-14 10:06:37
    data = c.tradedates(start_date, end_date, "period=1,order=2,market=CNSESH")
    traddate_list = data.Data
    return traddate_list


def get_codes_(start_row=0, end_row=1):
    """
     获取9个股票的code
     从 code_names_20210127.csv 中获取
    """
    code_ = pd.read_csv("../code_names_20210127.csv", usecols=[0])
    code_r = code_.iloc[start_row:end_row, :]
    res = np.array(code_r)
    res.tolist()
    return res


def search_codes_(name=None):
    """
    根据名称获取code
    """
    code_ = pd.read_csv("../code_names_20210127.csv", usecols=[0, 1, 2])
    # print(code_.head(5))
    if name is None:
        return False
    else:
        coder = code_[code_.NAME == name]
        res = np.array(coder)
        res.tolist()
        return res


def result_handler(data_res, secucode, traddate, con, table_name):
    if(data_res.ErrorCode != 0):
        print("request ctr Error, ", data_res.ErrorMsg)
    else:
        print("ctr输出结果======分割线======")
        # print(data_res.Codes)
        # print()
        # print(data_res.Dates)
        # print()
        # print(data_res.Indicators )
        # print()
        # print(data_res.Data)
        # for key,value in data_res.Data.items():
        #     print()
        #     print(value)
        # for v in value:
        #  print(v, " ", end="")
        # print()
        # 创建 DataFrame 结构数据
        label = data_res.Indicators
        label.insert(0, "SECUCODE")
        label.insert(1, "TRADEDATE")
        data_res_ = {}
        for key, value in data_res.Data.items():
            value.insert(0, secucode)
            value.insert(1, traddate)
            data_res_[key] = value

        df2 = pd.DataFrame()
        pd_data2 = dict_convert_dataframe(data_res_, label, df2)
        # # pd_data2.to_csv(file)
        # pd_data2.to_csv(file,mode="a",header=False)

        # 将数据写入数据库
        pd.io.sql.to_sql(pd_data2, table_name, con,
                         if_exists="append", index=False)


def mainGetData(is_main=None, mainstartDate=None, mainendDate=None, signal_name=None, tab_p=None, start_row=None, end_row=None):
    """
    获取数据业务处理主函数
    1.单个股票处理：mainstartDate=2021-03-17,mainendDate=2021-03-25,signal_name="拓斯达",tab_p="tsd"
    2.批量处理：
        is_main=1,mainstartDate=2021-03-01,mainendDate=2021-03-11,start_row=622, end_row=650
    """
    if mainstartDate is None and mainendDate is None:
        print("请填入开始和结束日期")
        return False

    try:
        # 调用登录函数（激活后使用，不需要用户名密码）
        loginResult = c.start("ForceLogin=1", '', mainCallback)
        if(loginResult.ErrorCode != 0):
            print("login in fail")
            exit()
        print(" 获取专题报表-沪深股通-港资机构持股明细")
        con = create_engine(
            'mysql+pymysql://root:root@localhost:3306/stack?charset=utf8')
        file_prefix = "sz_stock_hk_sharehold_detail"
        # file = file_prefix+"-"+startDate+"_"+endDate+".csv" #拼接输出csv的路径

        #startDate = "2021-03-01"
        #endDate = "2021-03-11"

        tradDate = getDateOfCycle(mainstartDate, mainendDate)  # 获取交易时间
        if is_main is None:
            # 处理单个股票的函数
            if signal_name is None and tab_p is None:
                print("请填入股票名称-中文名称和后缀")
                return False
            code_ = search_codes_(name=signal_name)
            trab_name_ = tab_p+file_prefix
            for td in tradDate:  # 获取最新的交易时间
                for code in code_:
                    # print(td)
                    # print(code)
                    res = getStockDataInfo(secucode=code[0], traddate=td)
                    result_handler(res, code[0], td, con, trab_name_)

        else:
            # 批量处理股票-根据日期处理
            if (start_row is None and end_row is None) or (end_row - start_row) < 1:
                print("请填入正确的起始行和结束行数")
                return False

            table_name = file_prefix+"-"+mainstartDate+"_"+mainendDate
            # 更改此处，选择不同的code值,明天622开始
            codes_num9 = get_codes_(start_row=start_row, end_row=end_row)
            for td in tradDate[0:1]:  # 按照最新日期进行交易
                for code in codes_num9:
                    # print(td,code[0])
                    res = getStockDataInfo(secucode=code[0], traddate=td)
                    result_handler(res, code[0], td, con, table_name)

    except Exception as ee:
        print("error >>>", ee)
        traceback.print_exc()
    else:
        print("获取专题报表-沪深股通-港资持股机构明细-end")

    finally:
        # 退出
        data_shareholder = logoutResult = c.stop()
        con.dispose()  # 关闭数据库连接


if __name__ == "__main__":

    
    # 批量处理
    
    mainGetData(is_main=1, mainstartDate="2021-03-01",
                mainendDate="2021-03-11", start_row=3200, end_row=3300)
    
    # 单个股票处理
    """
    mainGetData(mainstartDate="2021-04-12", mainendDate="2021-04-23",
                signal_name="拓斯达", tab_p="tsd")
    """
